package com.kx.yooli.kx.wechat.mapper.nplm;

import com.kx.yooli.kx.wechat.entity.nplm.LoanRepayPlan;
import com.kx.yooli.kx.wechat.entity.nplm.OverdueCase;
import com.kx.yooli.kx.wechat.entity.repayment.ActiveRepayOperate;
import com.mybatisflex.annotation.UseDataSource;
import com.mybatisflex.core.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

public interface ILoanPlanMapper extends BaseMapper<LoanRepayPlan> {
    @UseDataSource("ds4")
    @Select(" select p.loan_no AS loanNo,p.cust_name AS custName,p.LOAN_INSTALMENTS AS loanInstalments ,p.DUE_DATE as dueDate,p.PHASE_NUMBER as phaseNumber" +
            ",p.OVERDUE_DAYS overdueDays,p.REPAY_DATE repayDate" +
            ",(sum(n.loan_amount )/count(p.loan_no) -sum(p.INDEED_TOTAL)) AS plannedTotal,sum(p.INDEED_TOTAL) AS indeedTotal,sum(n.loan_amount )/count(n.loan_no) AS loanAmount, " +
            " (case when p.REPAY_STATUS ='1' then '已还款' else '逾期中' end) as contractStatus " +
            " FROM " +
            " nplm.NPLM_REPAY_PLAN p " +
            " INNER JOIN nplm.NPLM_LOAN_CONTRACT n ON n.loan_no = p.loan_no " +
            "where p.loan_no = #{loanNo,jdbcType=VARCHAR} and p.PHASE_NUMBER != 0 " +
            "GROUP BY p.loan_no,p.cust_name,p.LOAN_INSTALMENTS,p.DUE_DATE,p.PHASE_NUMBER,p.OVERDUE_DAYS,p.REPAY_DATE," +
            "(case when p.REPAY_STATUS ='1' then '已还款' else '逾期中' end)" +
            " order by p.loan_no,p.PHASE_NUMBER"
            )
    List<LoanRepayPlan> selectLoanByLoanNo(String loanNo);
    /**
     * 从贷后主动还款表取数据
     * @author jhon_wsq
     *
     */
    @UseDataSource("ds4")
    @Select(" select c.id as id,c.CUST_NAME as custName,c.LOAN_NO as loanNo,sum(c.LOAN_AMOUNT)/count(c.id) as loanAmount,sum(o.SUCCESS_AMOUNT) as successAmount" +
            " from nplm.NPLM_LOAN_CONTRACT c inner join nplm.NPLM_ACTIVE_REPAY_OPERATE o " +
            " on o.LOAN_NO = c.LOAN_NO  where c.LOAN_NO =#{loanNo,jdbcType=VARCHAR} and o.JS_RET_CODE='0000'  group by " +
            " c.id,c.CUST_NAME ,c.LOAN_NO")
    List<ActiveRepayOperate> selectActiveByLoanNo(String loanNo);
    /**
     * 从逾期表取数据
     * @author jhon_wsq
     *  select LOAN_AMOUNT as loanAmount,LOAN_NO as loanNo,CUST_NAME as custName,CERT_NO as certNo, PLANNED_TOTAL as plannedTotal,ONCE_SETTLE_AMOUNT as onceSettle,STATUS as status,UPDATE_TIME as updateTime
    **/
    @UseDataSource("ds4")
    @Select(" select LOAN_AMOUNT as loanAmount,LOAN_NO as loanNo,CUST_NAME as custName,CERT_NO as certNo," +
            "(case when PLANNED_TOTAL < ONCE_SETTLE_AMOUNT then ONCE_SETTLE_AMOUNT else PLANNED_TOTAL end) AS plannedTotal,STATUS as status,UPDATE_TIME as updateTime " +
            "from (select  c.LOAN_AMOUNT,o.LOAN_NO,o.CUST_NAME,o.CERT_NO, o.PLANNED_TOTAL,o.ONCE_SETTLE_AMOUNT,o.STATUS,o.UPDATE_TIME from nplm.NPLM_OVERDUE_CASE o" +
            "  inner join nplm.NPLM_LOAN_CONTRACT c on c.LOAN_NO = o.LOAN_NO where o.LOAN_NO =#{loanNo,jdbcType=VARCHAR}" +
            "  order by  o.UPDATE_TIME desc " +
            " ) where  ROWNUM =1 ")
    List<OverdueCase> selectOverDuCaseByLoanNo(String loanNo);
    @UseDataSource("ds4")
    @Select("SELECT SUM(REPAY_AMOUNT) FROM nplm.NPLM_REPAY_OFFLINE WHERE LOAN_NO = #{loanNo,jdbcType=VARCHAR}")
    BigDecimal selectOverDuCaseByLoanNoOffline(String loanNo);


}
